package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.friday.dao.DepartmentDao;
import com.friday.po.Department;
import com.friday.util.DBUtil;
import com.sun.org.apache.regexp.internal.recompile;

public class DepartmentDaoImpl implements DepartmentDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertDepartment(Department dep) throws Exception{
		String sql = "insert into department values(?, ?, ?, ?)";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, dep.getId());
			pStmt.setString(2, dep.getDepName());
			pStmt.setString(3, dep.getDepId());
			pStmt.setString(4, dep.getManager());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int deleteDepartmentById(String id) throws Exception {
		String sql = "delete from department where id = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int updateDepartment(Department dep) throws Exception {
		String sql = "update department set depname = ?, depId = ?, manager = ? where id = ?";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, dep.getDepName());
			pStmt.setString(2, dep.getDepId());
			pStmt.setString(3, dep.getManager());
			pStmt.setString(4, dep.getId());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public List<Department> queryAllDepartment() throws Exception {
		String sql = "select * from department";
		List<Department> list = new ArrayList<Department>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				String id = rs.getString("id");
				String depName = rs.getString("depname");
				String depId = rs.getString("depid");
				String manager = rs.getString("manager");
 				Department dep = new Department(id, depName, depId, manager);
				list.add(dep);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public Department queryDepartmentById(String id) throws Exception {
		String sql = "select * from department where id = ?";
		Department dep = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String depName = rs.getString("depname");
				String depId = rs.getString("depid");
				String manager = rs.getString("manager");
 				dep = new Department(id, depName, depId, manager);
 				break;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return dep;
	}

	@Override
	public int deleteDepartmentByDepId(String depId) throws Exception {
		String sql = "delete from department where depid = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, depId);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public Department queryDepartmentByDepId(String depId) throws Exception {
		String sql = "select * from department where depid = ?";
		Department dep = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, depId);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String depName = rs.getString("depname");
				String manager = rs.getString("manager");
 				dep = new Department(id, depName, depId, manager);
 				break;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return dep;
	}
}